﻿/****************************************************************************
Copyright (c) 2014-2015 凌惊雪 微信:Lingjingxue 邮箱:lingjingxue@sina.com QQ:271487457

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
****************************************************************************/

using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using UtilPublic.ServerLoger;

namespace UtilPublic.Mysql
{
    /// <summary>
    /// 数据库Mysql处理
    /// </summary>
    public class DbMysql
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private MySqlConnection DataBaseUser = null;

        /// <summary>
        /// 数据库指令
        /// </summary>
        private MySqlCommand DataBaseCmd;

        /// <summary>
        /// 连接状态
        /// </summary>
        public int DataBaseState = 0;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DbMysql()
        {
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public DbMysql(string DataSource, string Port, string UserId, string Password, string Database)
        {
            try
            {
                string ConnectionString = $"Data Source={DataSource};Port={Port};User Id={UserId};Password={Password};Database={Database}";
                DataBaseUser = new MySqlConnection(ConnectionString);
                DataBaseCmd = DataBaseUser.CreateCommand();
                DataBaseCmd.CommandType = CommandType.Text;
            }
            catch (Exception ex)
            {
                loger.Fatal("Mysql数据库初始化失败", ex);
            }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public DbMysql(string connectionstring)
        {
            try
            {
                DataBaseUser = new MySqlConnection(connectionstring);
                DataBaseCmd = DataBaseUser.CreateCommand();
                DataBaseCmd.CommandType = CommandType.Text;
            }
            catch (Exception ex)
            {
                loger.Fatal("Mysql数据库初始化失败", ex);
            }
        }

        /// <summary>
        /// 连接到数据库
        /// </summary>
        /// <returns></returns>
        public void DbMysqlConnect()
        {
            //ServerLoger.Loger.Info($"数据库连接 {DataBaseUser.ConnectionString}");
            try
            {
                DataBaseCmd.Connection.Open();
                DataBaseState = 1;
                loger.Info("Mysql数据库连接成功");
            }
            catch
            {
                DataBaseState = 0;
                loger.Fatal("Mysql数据库连接失败");
            }
        }
        /// <summary>
        /// 判断是否连接
        /// </summary>
        /// <returns></returns>
        public bool IsConnect()
        {
            if (DataBaseState == 1)
            {
                return DataBaseUser.Ping();
            }
            return false;
        }

        /// <summary>
        /// 心跳循环内容
        /// </summary>
        public void LoopExecute()
        {
            TryReConnect();
        }
        /// <summary>
        /// 尝试重新连接
        /// </summary>
        /// <returns></returns>
        public bool TryReConnect()
        {
            if (DataBaseUser.Ping())
            {
                return true;
            }
            loger.Warn("Mysql数据库未连接  尝试重新连接......");
            DbMysqlConnect();
            if (DataBaseUser.Ping())
            {
                loger.Warn("重新连接......成功");
                return true;
            }
            loger.Error($"数据库重新连接失败");
            return false;
        }

        /// <summary>
        /// 执行数据库语句
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="immediately">是否及时处理</param>
        /// <returns></returns>
        public bool RunCommand(string cmd, bool immediately = false)
        {
            if (!immediately)
            {
                DbMysqlSecond.AddCmd(cmd);
                return true;
            }
            if (DataBaseState != 1)
            {
                return false;
            }
            try
            {
                DataBaseCmd.CommandText = cmd;
                DataBaseCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                loger.Error($"执行数据库错误 {cmd}", ex);
                return false;
            }
            return true;
        }

        /// <summary>
        /// 执行数据库查询
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public MySqlDataReader RunReader(string cmd)
        {
            if (DataBaseState != 1)
            {
                return null;
            }
            //ServerLoger.Loger.Debug($"{cmd}");
            DataBaseCmd.CommandText = cmd;
            return DataBaseCmd.ExecuteReader();
        }
        /// <summary>
        /// 执行数据库插入
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="datas">数据字典</param>
        /// <param name="immediately">是否立即</param>
        /// <returns></returns>
        public bool RunInsert(string tablename, Dictionary<string, object> datas, bool immediately = false)
        {
            if (datas.Count > 0)
            {
                var sbk = new StringBuilder();
                var sbv = new StringBuilder();
                int index = 0;
                foreach (var kvp in datas)
                {
                    index++;
                    sbk.Append(kvp.Key);
                    sbv.Append($"'{kvp.Value}'");
                    if (index != datas.Count)
                    {
                        sbk.Append(", ");
                        sbv.Append(", ");
                    }
                }
                string cmd = $"insert into {tablename} ({sbk}) values ({sbv})";
                return RunCommand(cmd, immediately);
            }
            return true;
        }

        /// <summary>
        /// 执行数据库更新
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="datas">数据字典</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="immediately">是否立即</param>
        /// <returns></returns>
        public bool RunUpdate(string tablename, Dictionary<string, object> datas, Dictionary<string, object> dataswhere, bool immediately = false)
        {
            if (datas.Count > 0)
            {
                var sbu = new StringBuilder();
                int index = 0;
                foreach (var kvp in datas)
                {
                    index++;
                    sbu.Append($"{kvp.Key}='{kvp.Value}'");
                    if (index != datas.Count)
                    {
                        sbu.Append(", ");
                    }
                }
                string cmd = $"update {tablename} set {sbu}";
                if (dataswhere.Count > 0)
                {
                    var sbw = new StringBuilder();
                    int indexw = 0;
                    foreach (var kvpw in dataswhere)
                    {
                        indexw++;
                        sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                        if (indexw != dataswhere.Count)
                        {
                            sbw.Append(" and ");
                        }
                    }
                    cmd += $" where ({sbw})";
                }
                return RunCommand(cmd, immediately);
            }
            return true;
        }

        /// <summary>
        /// 执行数据库增加/减少
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="mode">原数据</param>
        /// <param name="value">该变量</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="op">符号</param>
        /// <param name="immediately">是否立即</param>
        /// <returns></returns>
        public bool RunModify(string tablename, string mode, object value, Dictionary<string, object> dataswhere, string op = "+", bool immediately = true)
        {
            string cmd = $"update {tablename} set {mode} = {mode} {op} {value}";
            if (dataswhere.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                cmd += $" where ({sbw})";
            }
            return RunCommand(cmd, immediately);
        }

        /// <summary>
        /// 执行数据库查询存在
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataswhere">执行条件</param>
        /// <returns></returns>
        public bool RunCheckExist(string tablename, Dictionary<string, object> dataswhere)
        {
            bool isexist = false;
            string cmd = $"select * from {tablename}";
            if (dataswhere != null && dataswhere.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                cmd = $"select * from {tablename} where ({sbw})";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        isexist = true;
                    }
                    else
                    {
                        isexist = false;
                    }
                }
            }
            catch
            {
            }
            finally
            {
                reader.Close();
            }
            return isexist;
        }


        /// <summary>
        /// 执行数据库查询行数
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="sqlDateTime">sql时间</param>
        /// <returns></returns>
        public int RunCheckCount(string tablename, Dictionary<string, object> dataswhere, string sqlDateTime)
        {
            int result = 0;
            var sbw = new StringBuilder();
            if (dataswhere != null && dataswhere.Count > 0)
            {
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                if (!string.IsNullOrEmpty(sqlDateTime))
                {
                    sbw.Append($" and {sqlDateTime}");
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlDateTime))
                {
                    sbw.Append($"{sqlDateTime}");
                }
            }
            string cmd = $"select count(*) from {tablename}";
            if (sbw.Length > 0)
            {
                cmd = $"select count(*) from {tablename} where ({sbw})";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    result = reader.GetInt32(0);
                    break;
                }
            }
            catch
            {
            }
            finally
            {
                reader.Close();
            }
            return result;
        }

        /// <summary>
        /// 执行数据库查询总数
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataname">统计数据项</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="sqlDateTime">sql时间</param>
        /// <returns></returns>
        public long RunCheckSum(string tablename, string dataname, Dictionary<string, object> dataswhere, string sqlDateTime)
        {
            long result = 0;
            var sbw = new StringBuilder();
            if (dataswhere != null && dataswhere.Count > 0)
            {
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                if (!string.IsNullOrEmpty(sqlDateTime))
                {
                    sbw.Append($" and {sqlDateTime}");
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlDateTime))
                {
                    sbw.Append($"{sqlDateTime}");
                }
            }
            string cmd = $"select sum({dataname}) from {tablename}";
            if (sbw.Length > 0)
            {
                cmd = $"select sum({dataname}) from {tablename} where ({sbw})";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    result = reader.GetInt64(0);
                    break;
                }
            }
            catch
            {
            }
            finally
            {
                reader.Close();
            }
            return result;
        }

        /// <summary>
        /// 执行数据库读取
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="datasread">返回字典</param>
        /// <returns></returns>
        public bool RunReader(string tablename, Dictionary<string, object> dataswhere, ref Dictionary<string, object> datasread)
        {
            bool isexist = false;
            string cmd = $"select * from {tablename} where 1=1";
            if (dataswhere != null && dataswhere.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                cmd = $"select * from {tablename} where ({sbw})";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        isexist = true;
                        var datasreadkeys = new List<string>(datasread.Keys);
                        foreach (var item in datasreadkeys)
                        {
                            datasread[item] = reader[item];
                        }
                    }
                    else
                    {
                        isexist = false;
                    }
                }
            }
            catch (Exception ex)
            {
                loger.Fatal($"执行数据库读取错误", ex);
            }
            finally
            {
                reader.Close();
            }
            return isexist;
        }

        /// <summary>
        /// 执行数据库读取
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="datasreadkeys">读取的key</param>
        /// <param name="listdatasread">返回结果</param>
        /// <returns></returns>
        public bool RunReader(string tablename, Dictionary<string, object> dataswhere, List<string> datasreadkeys, out List<Dictionary<string, object>> listdatasread)
        {
            listdatasread = new List<Dictionary<string, object>>();
            bool isexist = false;
            string cmd = $"select * from {tablename}";
            if (dataswhere != null && dataswhere.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                cmd = $"select * from {tablename} where ({sbw})";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        isexist = true;
                        var datasread = new Dictionary<string, object>();
                        foreach (var item in datasreadkeys)
                        {
                            datasread[item] = reader[item];
                        }
                        listdatasread.Add(datasread);
                    }
                    else
                    {
                        isexist = false;
                    }
                }
            }
            catch (Exception ex)
            {
                loger.Fatal($"执行数据库读取错误", ex);
            }
            finally
            {
                reader.Close();
            }
            return isexist;
        }

        /// <summary>
        /// 执行数据库排序读取
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="listorders">排序条件</param>
        /// <param name="limit">选择个数</param>
        /// <param name="datasreadkeys">读取的key</param>
        /// <param name="listdatasread">返回结果</param>
        /// <returns></returns>
        public bool RunOrderReader(string tablename, Dictionary<string, string> listorders, int limit, List<string> datasreadkeys, ref List<Dictionary<string, object>> listdatasread)
        {
            bool isexist = false;
            string cmd = $"select * from {tablename} limit {limit}";
            if (listorders.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in listorders)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key} {kvpw.Value}");
                    if (indexw != listorders.Count)
                    {
                        sbw.Append(", ");
                    }
                }
                cmd = $"select * from {tablename} ORDER BY {sbw} limit {limit}";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        isexist = true;
                        var datasread = new Dictionary<string, object>();
                        foreach (var item in datasreadkeys)
                        {
                            datasread[item] = reader[item];
                        }
                        listdatasread.Add(datasread);
                    }
                    else
                    {
                        isexist = false;
                    }
                }
            }
            catch (Exception ex)
            {
                loger.Fatal($"执行数据库读取错误", ex);
            }
            finally
            {
                reader.Close();
            }
            return isexist;
        }

        /// <summary>
        /// 执行数据库读取
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="sqlDateTime">时间条件</param>
        /// <returns>返回结果</returns>
        public DataTable RunReadDataTable(string tablename, Dictionary<string, object> dataswhere, string sqlDateTime)
        {
            if (DataBaseState != 1)
            {
                return null;
            }

            try
            {
                var sbw = new StringBuilder();
                if (dataswhere != null && dataswhere.Count > 0)
                {
                    int indexw = 0;
                    foreach (var kvpw in dataswhere)
                    {
                        indexw++;
                        sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                        if (indexw != dataswhere.Count)
                        {
                            sbw.Append(" and ");
                        }
                    }
                    if (!string.IsNullOrEmpty(sqlDateTime))
                    {
                        sbw.Append($" and {sqlDateTime}");
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(sqlDateTime))
                    {
                        sbw.Append($"{sqlDateTime}");
                    }
                }
                string cmd = $"select * from {tablename}";
                if (sbw.Length > 0)
                {
                    cmd = $"select * from {tablename} where ({sbw})";
                }
                MySqlDataAdapter da = new MySqlDataAdapter(cmd, DataBaseUser);
                DataSet MyDataSet = new DataSet();
                da.Fill(MyDataSet);
                return MyDataSet.Tables[0];
            }
            catch (Exception ex)
            {
                loger.Fatal($"执行数据库读取错误", ex);
                return null;
            }
        }
    }
    

}